package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import model.Customer;
import model.Employee;
import model.Enterprise;
import model.Person;
import model.SubjectAttribute;
import model.type.SubjectType;

public class SubjectAttributeDAO {
	protected static final String TABLE = "subject_attribute";	
	private static String onSqlError = "SQL statement fault";
		

	protected static final String 
		ID_FIELD = "subject_attribute",			
		SUBJECT_FIELD = "subject_fk",
		SUBJECT_ATTRIBUTE_TYPE_FIELD = "subject_attribute_type_fk",		
		SUBJECT_TYPE_FIELD = "subject_type_fk",
		ORDERBY_FIELD = "orderby",
		VALUE_TEXT_FIELD = "value_text",
		VALUE_NUMBER_FIELD = "value_number",
		VALUE_DATE_FIELD = "value_date",
		DATA_TYPE_FIELD = "data_type";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" " +
			"("+SUBJECT_FIELD+", "+SUBJECT_ATTRIBUTE_TYPE_FIELD+", "+SUBJECT_TYPE_FIELD+", "+ORDERBY_FIELD+", " 
			+VALUE_TEXT_FIELD+", "+VALUE_NUMBER_FIELD+", "+VALUE_DATE_FIELD+", "+DATA_TYPE_FIELD+") " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " 
			+SUBJECT_FIELD+" = ?, "+SUBJECT_ATTRIBUTE_TYPE_FIELD+" = ?, "+SUBJECT_TYPE_FIELD+" = ?, "+ORDERBY_FIELD+" = ?, "
			+VALUE_TEXT_FIELD+" = ?, "+VALUE_NUMBER_FIELD+" = ?, "+VALUE_DATE_FIELD+" = ?, "+DATA_TYPE_FIELD+" = ? " 
			+"WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
		} catch (SQLException e) {				
			System.exit(0);
		}
	}
	
	
	public SubjectAttribute find(int id) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" = "+id);				
			SubjectAttribute s = extractSubjectAttribute(st.getResultSet()).get(0);
			return s;
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}
	
	public ArrayList<SubjectAttribute> findByEnterpriseId(Enterprise ent) {			
		return findByIdAndType(ent.getId(), SubjectType.ENTERPRISE);
	}
	
	public ArrayList<SubjectAttribute> findByPersonId(Person p) {			
		return findByIdAndType(p.getId(), SubjectType.PERSON);				
	}
	
	public ArrayList<SubjectAttribute> findByCustomerId(Customer c) {			
		return findByIdAndType(c.getId(), SubjectType.CUSTOMER);				
	}
	
	public ArrayList<SubjectAttribute> findByEmployeeId(Employee e) {			
		return findByIdAndType(e.getId(), SubjectType.EMPLOYEE);				
	}
	
	private ArrayList<SubjectAttribute> findByIdAndType(int subjectId, int subjectType) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+SUBJECT_FIELD+" = "+subjectId+
					" AND "+SUBJECT_TYPE_FIELD+" = "+subjectType);				
			return extractSubjectAttribute(st.getResultSet());			
		} catch (SQLException | IndexOutOfBoundsException e){
				System.out.println(onSqlError);				
				return null;
		}		
	}	
	
	private ArrayList<SubjectAttribute> extractSubjectAttribute(ResultSet r)
			throws SQLException {
		ArrayList<SubjectAttribute> list = new ArrayList<SubjectAttribute>();
		while(r.next()){		
			Date valueDate = r.getDate(8) != null ? new Date(r.getDate(8).getTime()): null;
			SubjectAttribute s = new SubjectAttribute(
				r.getInt(1),
				r.getInt(2),
				r.getInt(3),
				r.getInt(4),
				r.getInt(5),
				r.getString(6),
				r.getInt(7),
				valueDate,
				r.getInt(9)
			);
			list.add(s);
		}
		return list;
	}	
	
	public void insert(SubjectAttribute s){
		System.out.println(s.getId());
		System.out.println(s.getSubjectAttributeType());
		System.out.println(s.getValue());
		
		try{
			composeStatement(insertStatement, s);	
		    insertStatement.executeUpdate();			
		} catch (SQLException e){
				System.out.println(onSqlError);								
		}		
	}
	
	public void update(SubjectAttribute s){
		try{										
			composeStatement(updateStatement, s);	       
		    updateStatement.setInt(9, s.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException e){
				System.out.println(onSqlError);								
		}			
	}
	
	private void composeStatement(PreparedStatement statement, SubjectAttribute s) throws SQLException{		
		java.sql.Date valueDate = s.getValueDate() != null ? new java.sql.Date(s.getValueDate().getTime()) : null;
	    statement.setInt(1, s.getSubject());
	    statement.setInt(2, s.getSubjectAttributeType());
	    statement.setInt(3, s.getSubjectType());
	    statement.setInt(4, s.getOrderby());
	    statement.setString(5, s.getValueText());
	    statement.setInt(6, s.getValueNumber());		    
	    statement.setDate(7, valueDate);		
	    statement.setInt(8, s.getDataType());
	}
	
	public void delete(SubjectAttribute s){
		try{										
		    deleteStatement.setInt(1, s.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException e){
				System.out.println(onSqlError);					
		}			
	}	
	
}
